package com.j3.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.Logger;

import com.j3.db.ConnectionManager;
import com.j3.db.DataSourceUnRegisterException;
import com.j3.db.Db;
import com.j3.string.StringUtils;

/*
 * 数据库结果公共类
 */
public class GridUtils {
    private static final Logger LOGGER = Logger.getLogger(GridUtils.class);

    private int totalRows = 0;
    private int fetchRows = 0;
    private int cursor = -1;
    private String sql = null;
    private String order = null;
    private Object[][] result;// 返回结果集合
    private String[] columns = null;// 返回列名集合
    private Map<String, Integer> columnIndexCache;// 列索引缓存
    private Object[] parameters; // 查询参数
    private int[] parameterTypes; // 参数类型
    private String[] columnTypeNames = null;
    private int[] columnTypes = null; //
    private boolean safeReplace = false; // 是否正常传参
    private final static Log log = LogFactory.getLog(GridUtils.class);
    public boolean isSafeReplace() {
        return safeReplace;
    }

    public void setSafeReplace(boolean safeReplace) {
        this.safeReplace = safeReplace;
    }

    public GridUtils() {
    }

    public void clear() {
        this.result = null;
        this.columns = null;
        this.columnTypeNames = null;
        this.columnTypes = null;
        this.totalRows = 0;
        this.fetchRows = 0;
        this.parameters = null;
        this.parameterTypes = null;
        this.cursor = -1;
        this.sql = null;
        this.order = null;
    }

    public void setSQL(String sql) {
        this.sql = sql;
    }

    public int getTotalRows() {
        return this.totalRows;
    }

    public int getFetchRows() {
        return this.fetchRows;
    }

    public int[] getColumnTypes() {
        return columnTypes;
    }

    public int getColumnType(int index) {
        if (this.columnTypes == null) {
            return -1;
        }
        return this.columnTypes[index];
    }

    public String[] getColumnTypeNames() {
        return columnTypeNames;
    }

    public String getColumnTypeName(int index) {
        if (this.columnTypeNames == null) {
            return "";
        }
        return this.columnTypeNames[index];
    }

    public String[] getColumns() {
        return columns;
    }

    public int getFieldCounts() {
        if (this.columns == null) {
            return 0;
        }
        return this.columns.length;
    }

    private void process(Connection conn) throws SQLException {

        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(getSQL(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (parameters != null && parameterTypes != null) {
                for (int i = 0; i < parameters.length; i++) {
                    ps.setObject(i + 1, parameters[i], parameterTypes[i]);
                }
            }
            rs = ps.executeQuery();
            this.processResultSet(rs);
        }
        finally {
            releaseConn(conn, ps, rs);
        }
    }

    public void init(Db db, String sql) throws SQLException {
        Connection conn = ConnectionManager.getConn(db);
        this.sql = sql;
        process(conn);
    }
    
    public static int update(String sql) throws SQLException {
    	Connection conn = null;
        try {
        	log.info("update " + sql);
			conn = ConnectionManager.getConn();       
			return conn.createStatement().executeUpdate(sql);
		} catch (DataSourceUnRegisterException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			log.error(sql);
			return 0;
		}finally{
			ConnectionManager.close(conn);
		}
    }

    public void init(Db db, String sqlStr, List<Object> parameters) throws SQLException {
        Connection conn = ConnectionManager.getConn(db);
        processWithoutParameterType(conn, sqlStr, parameters);
    }

    public void init(String sql) throws SQLException {
        init(null, sql);
    }

    public void init(String sql, Object[] parameters, int[] parameterTypes) throws SQLException {
        this.parameters = parameters;
        this.parameterTypes = parameterTypes;
        init(null, sql);
    }

    public void init(Db db, String sql, Object[] parameters, int[] parameterTypes) throws SQLException {
        this.parameters = parameters;
        this.parameterTypes = parameterTypes;
        init(db, sql);
    }

    public Object getObject(String field) {
        return this.getObject(this.cursor, field);
    }

    public Object getObject(int index) {
        return this.getObject(this.cursor, index);
    }

    public Object getObject(int rowNo, String field) {
        if (rowNo >= this.fetchRows || rowNo < 0) {
            LOGGER.error("Column index out of range");
            return null;
        }
        if (this.columns == null) {
            LOGGER.error("No column is available");
            return null;
        }
        return getObject(rowNo, findColumnIndex(field));
    }

    /*
     * 功能描述：根据列名查询出 列所在的索引
     */
    private int findColumnIndex(String field) {
        Integer index = columnIndexCache.get(field);
        if (index == null) {
            LOGGER.error(String.format("Column [%s] does not exist,  sql = %s", field, this.getSQL()));
            return -1;
        }
        return index;
    }

    public Object getObject(int rowNo, int index) {
        if (index < 0 || index >= this.columns.length) {
            LOGGER.error("Column Index out of range :" + index);
            return null;
        }
        return result[rowNo][index];
    }

    public List<String> fetchColumnToList(String field) {
        List<String> list = new ArrayList<String>();
        for (int i = 0; i < this.getFetchRows(); i++) {
            list.add(this.get(i, field));
        }
        return list;
    }

    public String get(int rowNo, String field, boolean safeReplace) {
        this.setSafeReplace(safeReplace);
        return get(rowNo, field);
    }

    public String get(String field, boolean safeReplace) {
        this.setSafeReplace(safeReplace);
        return get(field);
    }

    /*
     * 获得某一行rowNo 列名为field的值
     */
    public String get(int rowNo, String field) {
        return get(rowNo, findColumnIndex(field));
    }

    public String get(int rowNo, int colIndex) {
        Object obj = getObject(rowNo, colIndex);
        if (obj == null) {
            return "";
        }
        if (safeReplace) {
            return StringUtils.safeReplace(obj.toString());
        }
        return obj.toString();
    }

    public String get(String field) {
        return get(this.cursor, field);
    }

    public String get(int colIndex) {
        return get(this.cursor, colIndex);
    }

    public long getLong(int rowNo, String field, long defaultValue) {
        return getLong(rowNo, findColumnIndex(field), defaultValue);
    }

    public long getLong(int rowNo, int index, long defaultValue) {
        Object obj = getObject(rowNo, index);
        if (isEmptyObject(obj)) {
            return defaultValue;
        }
        try {
            return Long.parseLong(obj.toString());
        }
        catch (Exception e) {
            return defaultValue;
        }
    }

    public long getLong(int rowNo, String field) {
        return getLong(rowNo, field, 0);
    }

    public long getLong(String field) {
        return getLong(this.cursor, field);
    }

    public short getShort(int rowNo, int colIndex, short defaultValue) {
        Object obj = getObject(rowNo, colIndex);
        if (isEmptyObject(obj)) {
            return defaultValue;
        }
        try {
            return Short.parseShort(obj.toString());
        }
        catch (Exception e) {
            return defaultValue;
        }
    }

    public short getShort(int rowNo, String field, short defaultValue) {
        return getShort(rowNo, findColumnIndex(field), defaultValue);
    }

    public short getShort(int rowNo, String field) {
        return getShort(rowNo, field, (short) 0);
    }

    public short getShort(String field) {
        return getShort(this.cursor, field);
    }

    public int getInt(int rowNo, String field, int defaultValue) {
        return getInt(rowNo, findColumnIndex(field), defaultValue);
    }

    public int getInt(int rowNo, String field) {
        return getInt(rowNo, field, 0);
    }

    public int getInt(int rowNo, int colIndex, int defaultValue) {
        Object obj = getObject(rowNo, colIndex);
        if (isEmptyObject(obj)) {
            return defaultValue;
        }
        try {
            return Integer.parseInt(obj.toString());
        }
        catch (Exception e) {
            return defaultValue;
        }
    }

    public int getInt(int rowNo, int colIndex) {
        return getInt(rowNo, colIndex, 0);
    }

    public int getInt(int colIndex) {
        return getInt(this.cursor, colIndex);
    }

    public int getInt(String field) {
        return getInt(this.cursor, field);
    }

    public int getInt(String field, int defaultValue) {
        return getInt(this.cursor, field, defaultValue);
    }

    private boolean isEmptyObject(Object obj) {
        return obj == null || "".equals(obj.toString());
    }

    public float getFloat(int rowNo, String field, float defaultValue) {
        return getFloat(rowNo, findColumnIndex(field), defaultValue);
    }

    public float getFloat(int rowNo, String field) {
        return getFloat(rowNo, field, 0);
    }

    public float getFloat(int rowNo, int colIndex, float defaultValue) {
        Object obj = getObject(rowNo, colIndex);
        if (obj == null) {
            return defaultValue;
        }
        try {
            return Float.parseFloat(obj.toString());
        }
        catch (Exception e) {
            return defaultValue;
        }
    }

    public float getFloat(int rowNo, int colIndex) {
        return getFloat(rowNo, colIndex, 0);
    }

    public float getFloat(int colIndex) {
        return getFloat(this.cursor, colIndex);
    }

    public float getFloat(String field) {
        return getFloat(this.cursor, field);
    }

    public float getFloat(String field, float defaultValue) {
        return getFloat(this.cursor, field, defaultValue);
    }

    public byte getByte(int rowNo, String colName, byte defaultValue) {
        return getByte(rowNo, findColumnIndex(colName), defaultValue);
    }

    public byte getByte(int rowNo, int colIndex, byte defaultValue) {
        Object obj = getObject(rowNo, colIndex);
        if (!(isEmptyObject(obj))) {
            try {
                return Byte.parseByte(obj.toString());
            }
            catch (NumberFormatException nfe) {
            }
        }
        return defaultValue;
    }

    public byte getByte(int rowNo, String colName) {
        return getByte(rowNo, colName, (byte) 0);
    }

    public byte getByte(String colName) {
        return getByte(this.cursor, colName);
    }

    public byte getByte(int colIndex) {
        return getByte(this.cursor, colIndex, (byte) 0);
    }

    public double getDouble(int rowNo, String field, double defaultValue) {
        return getDouble(rowNo, findColumnIndex(field), defaultValue);
    }

    public double getDouble(int rowNo, String field) {
        return getDouble(rowNo, field, 0);
    }

    public double getDouble(int rowNo, int colIndex, double defaultValue) {
        Object obj = getObject(rowNo, colIndex);
        if (obj == null) {
            return defaultValue;
        }
        try {
            return Double.parseDouble(obj.toString());
        }
        catch (Exception e) {
            return defaultValue;
        }
    }

    public double getDouble(int rowNo, int colIndex) {
        return getDouble(rowNo, colIndex, 0);
    }

    public double getDouble(int colIndex) {
        return getDouble(this.cursor, colIndex);
    }

    public double getDouble(String field) {
        return getDouble(this.cursor, field);
    }

    public double getDouble(String field, double defaultValue) {
        return getDouble(this.cursor, field, defaultValue);
    }

    public Timestamp getTimestamp(int rowNo, String field, Timestamp defaultValue) {
        return getTimestamp(rowNo, findColumnIndex(field), defaultValue);
    }

    public Timestamp getTimestamp(int rowNo, int colIndex, Timestamp defaultValue) {
        Object obj = getObject(rowNo, colIndex);
        if (obj == null) {
            return defaultValue;
        }
        return (Timestamp) obj;
    }

    public Timestamp getTimestamp(int rowNo, String field) {
        return getTimestamp(rowNo, field, null);
    }

    public Timestamp getTimestamp(String field) {
        return getTimestamp(this.cursor, field);
    }

    public Timestamp getTimestamp(String field, Timestamp defaultValue) {
        return getTimestamp(this.cursor, field, defaultValue);
    }

    public Timestamp getTimestamp(int rowNo, int colIndex) {
        return getTimestamp(this.cursor, colIndex, null);
    }

    public String getDateString(int rowNo, String field) {
        return getDateString(rowNo, findColumnIndex(field));
    }

    public String getDateString(String field) {
        return getDateString(this.cursor, field);
    }

    public String getDateString(int rowNo, int colIndex) {
        Timestamp date = getTimestamp(rowNo, colIndex);
        if (date == null) {
            return "";
        }
        return date.toString().substring(0, 10);
    }

    public String getTime(String field) {
        return getTime(this.cursor, field);
    }

    public String getTime(int rowNo, int colIndex) {
        Timestamp date = getTimestamp(rowNo, colIndex);
        if (date == null) {
            return "";
        }
        return new SimpleDateFormat("HH:mm:ss").format(date);
    }

    public String getTime(int rowNo, String field) {
        return getTime(rowNo, findColumnIndex(field));
    }

    public String getDateTimeString(String field) {
        return getDateTimeString(this.cursor, field);
    }

    public String getDateTimeString(int rowNo, String field) {
        return getDateTimeString(rowNo, findColumnIndex(field));
    }

    public String getDateTimeString(int rowNo, int colIndex) {
        Timestamp date = getTimestamp(rowNo, colIndex);
        if (date == null) {
            return "";
        }
        return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
    }

    public String getTimeString(int rowNo, String field) {
        return getTimeString(rowNo, findColumnIndex(field));
    }

    public String getTimeString(String field) {
        return getTimeString(this.cursor, field);
    }

    public String getTimeString(int rowNo, int colIndex) {
        Timestamp date = getTimestamp(rowNo, colIndex);
        if (date == null) {
            return "";
        }
        return date.toString().substring(0, 16);
    }

    public boolean next() {
        if (this.fetchRows == 0) {
            return false;
        }
        if (this.cursor >= -1 && this.cursor < (this.fetchRows - 1)) {
            this.cursor++;
            return true;
        } else {
            this.cursor = -1;
            return false;
        }
    }

    public int findRowIndex(String fieldName, String fieldValue) {
        if (this.result == null) {
            return -1;
        }
        for (int i = 0; i < this.getFetchRows(); i++) {
            if (fieldValue.equals(get(i, fieldName))) {
                return i;
            }
        }
        return -1;
    }

    public String findFieldValue(String fromField, String fromValue, String toField) {
        int index = findRowIndex(fromField, fromValue);
        if (index < 0) {
            return "";
        }
        return get(index, toField);
    }

    public String getSQL() {
        String query = this.sql;
        if (this.order != null && !"".equals(this.order)) {
            return query + " order by " + this.order;
        }
        return query;
    }

    public String getCountSQL() {
        StringBuilder builder = new StringBuilder("select count(0) as totalRows from ( ");
        builder.append(getSQL());
        builder.append(" ) as countTable ");
        return builder.toString();
    }

    private void processWithoutParameterType(Connection conn, String sql, List<Object> parameters) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < parameters.size(); i++) {
                Object obj = parameters.get(i);
                ps.setObject(i + 1, obj);
            }
            rs = ps.executeQuery();
            this.processResultSet(rs);
        }
        finally {
            releaseConn(conn, ps, rs);
        }
    }

    /**
     * @param conn
     * @param ps
     * @param rs
     * @throws SQLException
     */
    private void releaseConn(Connection conn, PreparedStatement ps, ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (ps != null) {
            ps.close();
        }
        ConnectionManager.close(conn);
    }

    protected void processResultSet(ResultSet rs) throws SQLException {
        rs.absolute(-1);
        this.totalRows = rs.getRow();
        rs.beforeFirst();

        ResultSetMetaData metaData = rs.getMetaData();
        int fieldCount = metaData.getColumnCount();
        columns = new String[fieldCount];
        columnIndexCache = new HashMap<String, Integer>();
        columnTypes = new int[fieldCount];
        columnTypeNames = new String[fieldCount];
        for (int i = 0; i < fieldCount; i++) {
            columns[i] = metaData.getColumnName(i + 1);
            columnTypes[i] = metaData.getColumnType(i + 1);
            columnTypeNames[i] = metaData.getColumnTypeName(i + 1);
            columnIndexCache.put(columns[i], i);
            columnIndexCache.put(metaData.getColumnLabel(i + 1), i);
        }
        Object obj;
        result = new Object[totalRows][fieldCount];
        fetchRows = 0;
        while (rs.next()) {
            for (int i = 0; i < columns.length; i++) {
                try {
                    obj = rs.getObject(i + 1);
                }
                catch (Exception e) {
                    obj = null;
                }
                result[fetchRows][i] = obj;
            }
            this.fetchRows++;
        }
    }

    public int getCursor() {
        return cursor;
    }

    public void setCursor(int cursor) {
        this.cursor = cursor;
    }

    public void setFetchRows(int fetchRows) {
        this.fetchRows = fetchRows;
    }

    public String getOrder() {
        return order;
    }

    public void setOrder(String order) {
        this.order = order;
    }

    public long getColumnSumLong(String filed) {
        long sum = 0;
        for (int i = 0; i < this.fetchRows; i++) {
            sum += getLong(i, filed);
        }
        return sum;
    }

    public int getColumnSumInt(String filed) {
        int sum = 0;
        for (int i = 0; i < this.fetchRows; i++) {
            sum += getInt(i, filed);
        }
        return sum;
    }

    public float getColumnSumFloat(String filed) {
        float sum = 0;
        for (int i = 0; i < this.fetchRows; i++) {
            sum += getFloat(i, filed);
        }
        return sum;
    }

    public double getColumnSumDouble(String filed) {
        double sum = 0;
        for (int i = 0; i < this.fetchRows; i++) {
            sum += getDouble(i, filed);
        }
        return sum;
    }

    public void setParameters(Object[] parameters) {
        this.parameters = parameters;
    }

    public Object[] getParameters() {
        return parameters;
    }

    public void setParameterTypes(int[] parameterTypes) {
        this.parameterTypes = parameterTypes;
    }

}
